﻿using System;
using System.Data;
using System.Threading;
using System.Web;

namespace PageCache.Store.SQLServer
{




    public class SQLServerStore : IStore
    {

        SQLServerHelper dbh = null;

        public string Name { get; set; }


        public SQLServerStore(string connectionString)
        {
            dbh = new SQLServerHelper(connectionString);
        }

        string getTableName(string key)
        {
            return "data_" + key.Substring(0, 2);
        }


        public bool Exists(string type, string key)
        {
            try
            {
                var exist = dbh.ExecuteScalar<object>("select top 1 1 from [" + getTableName(key) + "]  where [KEY]=@Key and [Type]=@Type"
                    , dbh.CreateParameter("@Key", key)
                    , dbh.CreateParameter("@Type", type));

                if (exist != null && exist != DBNull.Value)
                {
                    return true;
                }

            }
            catch (Exception ex)
            {

            }

            return false;
        }



        public void Save(Store.StoreData data)
        {

            string tableName = getTableName(data.Key);

            try
            {
                if (Exists(data.Type, data.Key))
                {
                    dbh.ExecuteNoneQuery("update [" + tableName + "] set [HeadersData]=@HeadersData,[BodyData]=@BodyData,[CreatedDate]=@CreatedDate,[ExpiresAbsolute]=@ExpiresAbsolute,[Seconds]=@Seconds where [KEY]=@KEY and [Type]=@Type"
                       , dbh.CreateParameter("@KEY", data.Key)
                       , dbh.CreateParameter("@Type", data.Type)

                       /*二进制数据*/
                       , dbh.CreateParameter("@HeadersData", data.HeadersData)
                       , dbh.CreateParameter("@BodyData", data.BodyData)
                       /*end 二进制数据*/

                       , dbh.CreateParameter("@CreatedDate", data.CreatedDate)
                       , dbh.CreateParameter("@ExpiresAbsolute", data.ExpiresAbsolute)
                       , dbh.CreateParameter("@Seconds", data.Seconds)
                       );
                }
                else
                {

                    dbh.ExecuteNoneQuery("insert into [" + tableName + "]([KEY],[Type],[HeadersData],[BodyData],[CreatedDate],[ExpiresAbsolute],[Seconds]) values(@KEY,@Type,@HeadersData,@BodyData,@CreatedDate,@ExpiresAbsolute,@Seconds);"
                    , dbh.CreateParameter("@KEY", data.Key)
                    , dbh.CreateParameter("@Type", data.Type)

                    /*二进制数据*/
                    , dbh.CreateParameter("@HeadersData", data.HeadersData)
                    , dbh.CreateParameter("@BodyData", data.BodyData)
                    /*end 二进制数据*/

                    , dbh.CreateParameter("@CreatedDate", data.CreatedDate)
                    , dbh.CreateParameter("@ExpiresAbsolute", data.ExpiresAbsolute)
                    , dbh.CreateParameter("@Seconds", data.Seconds)
                    );



                }
            }
            catch (Exception ex)
            {

            }


            DoClear(tableName);
        }



        public Store.StoreData GetData(string type, string key)
        {

            Store.StoreData data = null;

            string tableName = getTableName(key);

            string sql = "select [KEY],[Type],[CreatedDate],[ExpiresAbsolute],[Seconds],[HeadersData],[BodyData] from [" + tableName + "]   where [Type]=@Type and [Key]=@Key";

            try
            {
                using (var connection = dbh.CreateConnection())
                {
                    using (var command = dbh.CreateCommand())
                    {
                        command.CommandText = sql;
                        command.Connection = connection;

                        command.Parameters.Add(dbh.CreateParameter("@Type", type));
                        command.Parameters.Add(dbh.CreateParameter("@Key", key));

                        connection.Open();

                        using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
                        {

                            if (reader.Read())
                            {
                                data = new Store.StoreData();

                                data.Key = reader.GetString(0);
                                data.Type = reader.GetString(1);
                                data.CreatedDate = reader.GetDateTime(2);
                                data.ExpiresAbsolute = reader.GetDateTime(3);
                                data.Seconds = reader.GetInt32(4);

                                data.HeadersData = (byte[])reader.GetValue(5);
                                data.BodyData = (byte[])reader.GetValue(6);


                            }


                            reader.Close();
                            reader.Dispose();
                        }
                        command.Dispose();
                        connection.Close();
                    }


                    connection.Dispose();
                }
            }
            catch (Exception ex)
            {

            }


            return data;
        }



        public void Delete(string type, string key)
        {
            try
            {
                dbh.ExecuteNoneQuery("delete from [" + getTableName(key) + "] where [Key]=@Key and [Type]=@Type;"
              , dbh.CreateParameter("@Key", key)
              , dbh.CreateParameter("@Type", type));
            }
            catch (Exception ex)
            {

            }



        }

        const int do_clear_seconds = 3600;
        void DoClear(string tableName)
        {
            if (IsDoing(tableName))
            {
                return;
            }

            DateTime date = DateTime.Now;
            string cacheKey = "pagecache_sqlserver_store_prevdate_" + tableName;
            var obj = HttpRuntime.Cache.Get(cacheKey);
            if (obj != null)
            {
                date = (DateTime)obj;

            }
            else
            {
                HttpRuntime.Cache.Insert(cacheKey, DateTime.Now);
            }


            if ((DateTime.Now - date).TotalSeconds > do_clear_seconds)
            {
                HttpRuntime.Cache[cacheKey] = DateTime.Now;

                if (IsDoing(tableName))
                {
                    return;
                }

                ThreadPool.QueueUserWorkItem(DoClearAsync, tableName);
            }
        }

        const string doingCacheKey = "pagecache_sqlserver_store_doing";

        bool IsDoing(string tableName)
        {
            bool doing = false;
            var obj = HttpRuntime.Cache.Get(doingCacheKey + tableName);
            if (obj != null)
            {
                doing = (bool)obj;
            }
            else
            {
                HttpRuntime.Cache.Insert(doingCacheKey + tableName, false);
            }

            return doing;
        }


        void DoClearAsync(object o)
        {
            DoClearing((string)o);
        }




        void DoClearing(string tableName)
        {
            if (IsDoing(tableName))
            {
                return;
            }

            HttpRuntime.Cache[doingCacheKey + tableName] = true;

            var date = DateTime.Now;

            string sql = "select top 100 [KEY],[Type] from [" + tableName + "]   where [ExpiresAbsolute]<=@date";

            loop:

            bool have = false;

            HttpRuntime.Cache[doingCacheKey + tableName] = true;


            try
            {
                using (var reader = dbh.ExecuteReader(sql, dbh.CreateParameter("@date", date)))
                {
                    while (reader.Read())
                    {
                        have = true;

                        string Key = reader.GetString(0);

                        string Type = reader.GetString(1);

                        Delete(Type, Key);
                    }
                    reader.Close();
                    reader.Dispose();
                }
            }
            catch (Exception ex)
            {

            }



            if (have)
            {
                goto loop;
            }

            HttpRuntime.Cache[doingCacheKey + tableName] = false;
        }

    }



}